﻿/******************************************************   
创建者: 曹亚亮
创建时间: 2017-05-08
修改:
修改时间: 
功能: 区域属性占比统计API
 ******************************************************/

using com.nm.ApiServer;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using com.nm.library.Helper;
using george.Data;
using Nancy.Extensions;
using NHibernateVertica;
using PlanAPI.model;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using com.nm.library;
using VerticaAPI;

namespace PlanAPI
{
    [Description("区域属性占比统计API")]
    public class PLParentRateHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        /// Writer：曹亚亮
        /// Create Date：2017-05-08
        [MustLogin]
        [Description("区域属性占比统计API")]
        [ReturnType(typeof(PaperData<PLParentRateModel.ListModel>))]
        public ApiResult GetPLParentRateList([ParamDescription("年份")] string Yearv,[ParamDescription("月份")] string Monthv, [ParamDescription("维度:A-商品层 B-品类")] string Dimension)
        {
            try
            {
                if (string.IsNullOrEmpty(Yearv))
                {
                    return Error("请选择年份！");
                }
                else if (string.IsNullOrEmpty(Monthv))
                {
                    return Error("请选择月份！");
                }
                else if (string.IsNullOrEmpty(Dimension))
                {
                    return Error("请选择维度！");
                }

                DataTable dt = new DataTable();
                if (Dimension == "A")
                {
                    dt = ReturnPLParentRateListA(Yearv, Monthv);
                }
                else if (Dimension == "B")
                {
                    dt = ReturnPLParentRateListB(Yearv, Monthv);
                }
                else
                {
                    return Error("不存在此维度的数据！");
                }
                
                var list = ModelConvertHelper<PLParentRateModel.ListModel>.ConvertToModel(dt);
                return Success(list);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// Writer：曹亚亮
        /// Create Date：2017-05-08
        /// <summary>
        /// 区域属性占比统计 商品层列表
        /// </summary>
        /// <param name="Yearv">年份</param>
        /// <param name="Monthv">月份</param>
        /// <returns></returns>
        public DataTable ReturnPLParentRateListA(string Yearv, string Monthv)
        {
            try
            {
                var sql = $@"SELECT DISTINCT 
	CompanyID,ExtrSource,
	Yearv,
	Monthv,
	ParentAreaID,
	MaterialProperty AS ModelName,
	CAST((SUM(MatToParentRate) * 100) AS DECIMAL(38,2)) AS MatToParentRate
FROM(
	SELECT DISTINCT 
		CompanyID,ExtrSource,
		Yearv,
		Monthv,
		ParentAreaID,
		KindID,
		SeriesID,
		MaterialProperty,
		MatToParentRate
	FROM ngoods.ngoods_PL_ParentRate
	WHERE 1=1 
	AND Yearv='{Yearv}' 
	AND Monthv='{Monthv}'
) a
where CompanyID='{UserInfo.UserCompanyId}' and ExtrSource='{UserInfo.UserExtrSource}'
GROUP BY CompanyID,ExtrSource,Yearv,Monthv,ParentAreaID,MaterialProperty
ORDER BY CompanyID,ExtrSource,Yearv,Monthv,ParentAreaID,MaterialProperty ";

                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// Writer：曹亚亮
        /// Create Date：2017-05-08
        /// <summary>
        /// 区域属性占比统计 品类列表
        /// </summary>
        /// <param name="Yearv">年份</param>
        /// <param name="Monthv">月份</param>
        /// <returns></returns>
        public DataTable ReturnPLParentRateListB(string Yearv, string Monthv)
        {
            try
            {
                var sql = $@"SELECT DISTINCT 
	a.CompanyID,a.ExtrSource,
	a.Yearv,
	a.Monthv,
	a.ParentAreaID,
	b.ModelName,
	CAST((sum(a.ModelToParentRate) * 100) AS DECIMAL(38,2)) AS MatToParentRate
FROM(
	SELECT DISTINCT
		CompanyID,ExtrSource,
		Yearv,
		Monthv,
		ParentAreaID,
		KindID,
		SeriesID,
		MaterialProperty,
		ModelID,
		ModelToParentRate
	FROM ngoods.ngoods_PL_ParentRate
	WHERE 1=1 
	AND Yearv='{Yearv}' 
	AND Monthv='{Monthv}'
) a 
INNER JOIN nods.nods_erp_SD_Mat_Model b ON a.ModelID=b.ModelID and a.ExtrSource=b.ExtrSource
where a.CompanyID='{UserInfo.UserCompanyId}' and a.ExtrSource='{UserInfo.UserExtrSource}'
GROUP BY a.CompanyID,a.ExtrSource,a.Yearv,a.Monthv,a.ParentAreaID,b.ModelName
ORDER BY a.CompanyID,a.ExtrSource,a.Yearv,a.Monthv,a.ParentAreaID,b.ModelName ";

                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
    }
}
